import java.sql.Connection
import java.sql.DriverManager

//数据库地址
def url = "jdbc:oracle:thin:@10.10.66.71:1521:orcl"
//用户名
def username = "dfdev_crux20191209"
//密码
def password = "crux"
Class.forName("oracle.jdbc.driver.OracleDriver")
Connection conn = DriverManager.getConnection(url, username, password)

//组织类型ID
long orgTypeId = getOrgTypeId(conn)


def users = getOrgUserList(conn)
users.forEach({ user ->
    if (0 == user.roles.size()) {
        println("用户${user.name}(id:${user.id},code:${user.code})没有角色")
    } else {
        user.roles.forEach({ role ->
            def pas = getPA(user.id, role.id, orgTypeId, conn)
            if (pas.size() != 2) {
                println("用户${user.name}(id:${user.id},code:${user.code})在角色${role.name}(id:${role.id},code:${role.code})下的组织权限不是两个,目前是${pas.size()}个")
                println("分别是:----${pas*.orgId}")
            } else {
                def pa1 = pas.get(0)
                def pa2 = pas.get(1)
                boolean check = true
                if ((pa1.getOrgId() != user.getOrganization()) && (pa1.getOrgId() != user.getRegion())) {
                    println("用户${user.name}(id:${user.id},code:${user.code})在角色${role.name}(id:${role.id},code:${role.code})下的组织${pa1.getOrgId()}既不是用户的单位，也不是用户的所属区划")
                    check = false
                }
                if ((pa2.getOrgId() != user.getOrganization()) && (pa2.getOrgId() != user.getRegion())) {
                    println("用户${user.name}(id:${user.id},code:${user.code})在角色${role.name}(id:${role.id},code:${role.code})下的组织${pa2.getOrgId()}既不是用户的单位，也不是用户的所属区划")
                    check = false
                }
                if (check) {
                    println("用户${user.name}(id:${user.id},code:${user.code})在角色${role.name}(id:${role.id},code:${role.code})下的组织权限分配正确")
                }
            }
        })
    }
})
conn.close()


static List<PA> getPA(long userId, long roleId, long orgTypeId, Connection connection) {
    println("开始查询用户${userId}在角色${roleId}下的组织权限")
    def pas = []
    def ps = connection.prepareStatement("select id,resource_id from permission_per_assignment where subject_id=:userId and type_id=:orgTypeId and function_group_id=:roleId")
    ps.setLong(1, userId)
    ps.setLong(2, orgTypeId)
    ps.setLong(3, roleId)
    def rs = ps.executeQuery()
    while (rs.next()) {
        long id = rs.getLong("id")
        String resourceId = rs.getString("resource_id")
        if (resourceId.contains("in")) {
            def startIdx = resourceId.indexOf("(")
            def endIdx = resourceId.indexOf(")")
            def orgId = resourceId.substring(startIdx + 1, endIdx)
            PA pa = new PA(id: id, orgId: Long.parseLong(orgId), functionGroupId: roleId, userId: userId)
            pas.add(pa)
        }
    }
    rs.close()
    ps.close()
    return pas
}


static List<User> getOrgUserList(Connection connection) {
    println("开始查询所有的单位用户")
    List<User> users2 = []
    def ps = connection.prepareStatement("select id,code,name,organization,region from usercenter_user where BELONG_TYPE=:orgType")
    ps.setString(1, "ORGUNIT")
    def rs = ps.executeQuery()
    while (rs.next()) {
        long id = rs.getLong("id")
        String code = rs.getString("code")
        String name = rs.getString("name")
        long org = rs.getLong("organization")
        long region = rs.getLong("region")
        User user = new User(id: id, code: code, name: name, organization: org, region: region, roles: getRolesByUserId(id, connection))
        users2.add(user)
    }
    rs.close()
    ps.close()
    println("查询所有的单位用户完成，数量:${users2.size()}")
    return users2;
}


static List<Role> getRolesByUserId(long userId, Connection connection) {
    println("开始查询用户的角色:${userId}")
    def roles = []
    def ps2 = connection.prepareStatement("select ufg.function_group_id as roleId,fg.code as roleCode,fg.name as roleName from permission_user_func_group ufg left join permission_func_group fg on ufg.function_group_id=fg.id where ufg.user_id=:userId")
    ps2.setLong(1, userId)
    def rs2 = ps2.executeQuery()
    while (rs2.next()) {
        def roleId = rs2.getLong("roleId")
        def roleCode = rs2.getString("roleCode")
        def roleName = rs2.getString("roleName")
        Role role = new Role(id: roleId, code: roleCode, name: roleName)
        roles.add(role)
    }
    rs2.close()
    ps2.close()
    return roles
}


static long getOrgTypeId(Connection connection) {
    def orgTypePs = connection.prepareStatement("select id from permission_per_type where code=:code")
    orgTypePs.setString(1, "Organization")
    def rs = orgTypePs.executeQuery()
    rs.next()
    def orgTypeId = rs.getLong("id")
    rs.close()
    orgTypePs.close()
    return orgTypeId;
}

class User {
    long id;
    String code;
    String name;
    long organization;
    long region;
    List<Role> roles = new ArrayList<>()
}

class Role {
    long id;
    String code;
    String name;
}

class PA {
    long id;
    long orgId;
    long functionGroupId;
    long userId;
}
